﻿using System;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;


namespace FileUtility
{
    public class DBUtility
    {
        public static Guid GetBatchID(string dataType, SqlDatabase db, string logTable, string prefix, out bool IsNew)
        {
            string sql = "SELECT BatchID FROM " + logTable
                + "(NOLOCK) WHERE Filename='" + prefix
                + "' AND status=-1";
            DataSet ds = db.ExecuteDataSet(CommandType.Text, sql);
            Guid batchId;
            if (ds.Tables[0].Rows.Count > 0)
            {
                batchId = new Guid(ds.Tables[0].Rows[0][0].ToString());
                IsNew = false;
            }
            else
            {
                batchId = Guid.NewGuid();
                IsNew = true;
            }
            return batchId;
        }

        public static DataTable GetDataTableNoData(SqlDatabase db, string targetTableName)
        {
            string sql = "SELECT * FROM " + targetTableName
                + "(NOLOCK) WHERE 1=0";

            DataSet ds = db.ExecuteDataSet(CommandType.Text, sql);
            return ds.Tables[0];
        }

        public static void AddNewFileLog(SqlDatabase db, Guid batchId, string DataType, string DataSource, string fileName, int totalFiles, int FileCount, int RecordCount, int Status, string PlantCode)
        {
            object[] _params = { batchId, DataType, DataSource, fileName, totalFiles, FileCount, RecordCount, Status, "", PlantCode, "CreateNewFile" };
            db.ExecuteNonQuery("Save_TransferLogData_SP", _params);
            // string sqlText = "exec Save_TransferLogData_SP @BatchID={0},@DataType={1},@TransType={2},@DataSource={3},@FileName={4},";
        }

        public static void UpdateImportEndStatus(SqlDatabase db, Guid batchId)
        {
            db.ExecuteNonQuery(CommandType.Text, "exec Save_TransferLogData_SP @TransType='UpdateImportEnd',@BatchId='" + batchId.ToString() + "'");
        }

        public static void AddFileCount(SqlDatabase db, Guid batchId, int recordsCount)
        {
            db.ExecuteNonQuery(CommandType.Text, "exec Save_TransferLogData_SP @TransType='AddFileCounts',@BatchId='" + batchId.ToString() + "',@RecordsCount=" + recordsCount.ToString());
        }

        public static string ReadDBNameByPlant(SqlDatabase db, string PlantCode)
        {
            string sql = "SELECT * FROM SAPDBConfigM"
               + "(NOLOCK) WHERE PlantCode='" + PlantCode + "'";

            DataSet ds = db.ExecuteDataSet(CommandType.Text, sql);
            string dbName = "";
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    dbName = ds.Tables[0].Rows[0]["DBConnString"].ToString();
                }
            }
            return dbName;
        }

        public static void SaveException(SqlDatabase db, Guid batchId, string exceptionMsg, string fileName)
        {
            db.ExecuteNonQuery(CommandType.Text, "exec Save_TransferLogData_SP @TransType='Exception',@BatchId='" + batchId.ToString() + "',@Exception=N'" + exceptionMsg + "',@FileName='" + fileName + "'");
        }

        public static DataTable ReadProcessConfigByPlant(SqlDatabase db, string PlantCode, string DataType)
        {
            string sql = "SELECT * FROM SAPPlantProcessConfigM"
            + "(NOLOCK) WHERE PlantCode='" + PlantCode + "' AND DataType='" + DataType + "'";
            DataSet ds = db.ExecuteDataSet(CommandType.Text, sql);
            return ds.Tables[0];

        }

        public static DataTable ReadAllFileMonitorProcess(SqlDatabase db)
        {
            try
            {
                string sql = "SELECT * FROM FileMonitorProcessM(NOLOCK) WHERE IsActive=1 AND IsLocked=0";
                DataSet ds = db.ExecuteDataSet(CommandType.Text, sql);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static int LockProcess(SqlDatabase db, string ProcessName, string LockedBy)
        {
            try
            {
                string sql = string.Format("UPDATE FileMonitorProcessM SET IsLocked=1,LockedBy='{0}' WHERE ProcessName='{1}'", LockedBy, ProcessName);
                return db.ExecuteNonQuery(CommandType.Text, sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static bool CheckIsLastFile(SqlDatabase db, Guid batchId)
        {
            string sql = string.Format(" select  TotalFiles, FileCounts,BatchID from TransferLogT(nolock) where batchId='{0}'", batchId.ToString());
            DataSet ds = db.ExecuteDataSet(CommandType.Text, sql);
            bool flag = false;
            if (ds.Tables[0].Rows.Count > 0)
            {
                int TotalFiles, FileCounts;
                string strTotalFiles = ds.Tables[0].Rows[0]["TotalFiles"].ToString();
                string strFileCounts = ds.Tables[0].Rows[0]["FileCounts"].ToString();
                Int32.TryParse(strTotalFiles, out TotalFiles);
                Int32.TryParse(strFileCounts, out FileCounts);
                if (TotalFiles == FileCounts)
                    flag = true;
            }
            return flag;
        }

        public static void UpdateStatus( SqlDatabase db,Guid batchId,int status )
        {
            try
            {
                string sql = string.Format("UPDATE TransferLogT SET Status='{0}' WHERE BatchID='{1}'", status.ToString(), batchId.ToString());
                db.ExecuteNonQuery(CommandType.Text, sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        #region Private Method
        public static DataTable GetSchema(string dtName, string connStr)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr);
            conn.Open();
            string cmdtext = string.Format("select c.name,c.length from syscolumns c,systypes t,sysobjects o where t.xusertype=c.xtype and o.id=c.id and o.name='{0}' order by c.colid", dtName);
            DataTable dtSchema = new DataTable();
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(cmdtext, conn);
            System.Data.SqlClient.SqlDataAdapter dap = new System.Data.SqlClient.SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            dap.Fill(ds);
            dtSchema = ds.Tables[0];
            conn.Close();
            return dtSchema;
        }
        #endregion
    }
}
